package connectivity;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import model.*;

public class QueryManager {

    private final Dbmanager dbmanager;

    public QueryManager(Dbmanager dbmanager) {
        this.dbmanager = dbmanager;
    }

    public String getCategoryName(int categoryId) {
        String categoryName = "";
        try {
            String sql = "SELECT naam FROM categorie WHERE categorie_id='" + categoryId + "'";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                categoryName = result.getString("naam");
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return categoryName;
    }

    public List<Category> getCategories() {
        List<Category> categories = new ArrayList<Category>();
        try {
            String sql = "SELECT * FROM categorie ORDER BY naam ASC";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                Category category = new Category(result.getInt("categorie_id"),
                        result.getString("naam"),
                        result.getString("omschrijving"));
                categories.add(category);
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return categories;
    }

    public Product getProduct(int productId) {
        Product product = new Product();
        try {
            String sql = "SELECT * FROM product WHERE product_id='" + productId + "'";
            ResultSet result = dbmanager.doQuery(sql);
            if (result.next()) {
                product = new Product(result.getInt("product_id"),
                        result.getInt("categorie_id"),
                        result.getString("naam"),
                        result.getString("omschrijving"),
                        result.getDouble("prijs"));
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return product;
    }
    
    //add Graeham
    public Category getCategory(int categoryId) {
        Category categorie = new Category();
        try {
            String sql = "SELECT * FROM categorie WHERE categorie_id='" + categoryId + "'";
            ResultSet result = dbmanager.doQuery(sql);
            if (result.next()) {
                categorie = new Category(
                        result.getInt("categorie_id"),
                        result.getString("naam"),
                        result.getString("omschrijving"));
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return categorie;
    }
    
    //Deze is nodig omdat de jcombobox niet de juiste dingen returnde
    public int getCategoryIdByName(String name) {
        int categoryId = 0;
        try {
            String sql = "SELECT categorie_id FROM categorie WHERE naam='" + name + "'";
            ResultSet result = dbmanager.doQuery(sql);
            if(result.next()) {
                categoryId = result.getInt("categorie_id");
            }
            
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return categoryId;
    }
    
    /*
     * Gets the id of the role by name
     * @param String name
     * @return int
     */
    public int getRoleIdByName(String name) {
        int roleId = 0;
        try {
            String sql = "SELECT role_id FROM rol WHERE naam='" + name + "'";
            ResultSet result = dbmanager.doQuery(sql);
            if(result.next()) {
                roleId = result.getInt("role_id");
            }
            
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return roleId;
    }
    
    /*
     * Gets the name of the role by id
     * @param Int id
     * @return String
     */
    public String getRoleNameById(int id) {
        String roleName = "";
        try {
            String sql = "SELECT naam FROM rol WHERE role_id=" + id + "";
            ResultSet result = dbmanager.doQuery(sql);
            if(result.next()) {
                roleName = result.getString("naam");
            }
            
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return roleName;
    }
    
    public List<String> getRoleNames() {
        List<String> names = new ArrayList<String>();
        try {
            String query = "SELECT naam FROM rol";
            ResultSet result = dbmanager.doQuery(query);
            while (result.next()) {
                names.add(result.getString("naam"));
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return names;
    }

    /*
     * Fetch user data by username and password.
     */
    public User getUser(String username, String password) {
        User user = new User();
        try {
            String sql = "SELECT * FROM gebruiker WHERE username='" + username + "' AND password='" + password + "'";
            //System.out.println(sql);
            ResultSet result = dbmanager.doQuery(sql);
            if (result.next()) {
                user = new User(result.getInt("gebruiker_id"),
                        result.getInt("rol_id"),
                        result.getString("firstname"),
                        result.getString("lastname"),
                        result.getString("username"),
                        result.getString("password"));
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return user;
    }
    //returns an array containing all users in the database.
    //IF THE FIRST USER HAS -1 as USERID, SHIT IS GOING RONG.

    public User[] getUserList() {
        User[] userList;
        try {
            String sql = "SELECT * FROM gebruiker ORDER BY gebruiker_id ASC";
            //System.out.println(sql);
            ResultSet result = dbmanager.doQuery(sql);  //Get the resultset
            result.last();      //set the resultset to the last row
            userList = new User[result.getRow()];   //get the number of the row to determine the size of the array.
            result.first();    //set the resultset back to the first row.
            int i = 0; //used for keeping track of current index of the resultset/array.
            while (i < userList.length) { //check whether i still is a correct index.
                //Create user object using data of current Row in resultSet.
                User user = new User(result.getInt("gebruiker_id"),
                        result.getInt("rol_id"),
                        result.getString("firstname"),
                        result.getString("lastname"),
                        result.getString("username"),
                        result.getString("password"));
                userList[i] = user;//add User object to the correct index of the Array.
                i++;// i + 1 to keep track of current index of Array.
                result.next(); //move the cursor of the resultset one row further.
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
            userList = new User[]{new User(-1, -1, "", "", "", "")};
        }
        System.out.println(userList.length);
        return userList;
    }

    public void deleteUser(int userId) {
        String sql = "DELETE FROM gebruiker WHERE gebruiker_id=" + userId;
        dbmanager.insertQuery(sql);
    }
  
    //delete product
    public void deleteProduct(int productId) {
        String sql = "DELETE FROM product WHERE product_id=" + productId;
        dbmanager.insertQuery(sql);
    }
    
    

    public void updateUser(User user) {
        String SQL = "UPDATE gebruiker SET rol_id='" + user.getRoleId() + "', firstname='"
                + user.getFirstname() + "', lastname='" + user.getLastname()
                + "', username='" + user.getUsername() + "', password='" + user.getPassword()
                + "' WHERE gebruiker_id='"
                + user.getUserId() + "'";
        dbmanager.insertQuery(SQL);
    }

    public void addUser(User user) {
        String SQL = "INSERT INTO gebruiker (rol_id, firstname, lastname, username, password)" + " VALUES('"
                + user.getRoleId() + "', '" + user.getFirstname()
                + "','" + user.getLastname() + "','" + user.getUsername() + "','"
                + user.getPassword() + "')";
        dbmanager.insertQuery(SQL);
    }
    
    public void addProduct(Product product) {
        String SQL = "INSERT INTO product (categorie_id, naam, prijs, omschrijving) VALUES ("
                + product.getCategorieId() + ", '" + product.getName() + "', " + product.getPrice() + ","
                + "'" + product.getDescription() + "')";
        dbmanager.insertQuery(SQL);
    }
    
    //Add new Category
    public void addCategorys(Category category) {
        String SQL = "INSERT INTO categorie (naam, omschrijving)" + " VALUES('"
                + category.getName() + "','"
                + category.getDescription() + "')";
        dbmanager.insertQuery(SQL);
    }
    
    
    
    public void addCategory(Category category) {
        String SQL = "INSERT INTO categorie (naam, omschrijving) VALUES ("
                + category.getName() + "', " + category.getDescription() + "')";
        dbmanager.insertQuery(SQL);
    }

    public Customer getCustomer(int klantId) {
        Customer klant = new Customer();
        try {
            String sql = "SELECT * FROM klant WHERE klant_id='" + klantId + "'";
            ResultSet result = dbmanager.doQuery(sql);
            if (result.next()) {
                klant = new Customer(result.getInt("klant_id"),
                        result.getString("naam"),
                        result.getString("adres"),
                        result.getString("postcode"),
                        result.getString("woonplaats"));
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return klant;
    }
    
    /*
     * Returns a list of all the customers
     * @return List<Customer>
     */
    public List<Customer> getCustomerList() {
        List<Customer> klanten = new ArrayList<Customer>();
        try {
            //Order by id.
            String sql = "SELECT * FROM klant ORDER BY klant_id ASC";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                klanten.add(new Customer(result.getInt("klant_id"),
                        result.getString("naam"),
                        result.getString("adres"),
                        result.getString("postcode"),
                        result.getString("woonplaats")));
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return klanten;
    }

    /*
     * Adds the customer
     * @param Customer customer
     */
    public void addCustomer(Customer customer) {
        String SQL_Klant = "INSERT INTO klant (naam, adres, postcode, woonplaats)" + " VALUES('"
                + customer.getName() + "', '" + customer.getAddress()
                + "','" + customer.getPostalCode() + "','" + customer.getResidence() + "')";
        dbmanager.insertQuery(SQL_Klant);
    }

    /*
     * Updates the customer
     * @param Customer customer
     */
    public void updateCustomer(Customer customer) {
        String SQL = "UPDATE klant SET naam='" + customer.getName() + "', adres='"
                + customer.getAddress() + "', postcode='" + customer.getPostalCode()
                + "', woonplaats='" + customer.getResidence() + "' WHERE klant_id='"
                + customer.getId() + "'";
        dbmanager.insertQuery(SQL);
    }

    /*
     * Deletes the customer
     * @param Customer customer
     */
    public void deleteCustomer(Customer customer) {
        String query = "DELETE FROM klant WHERE klant_id=" + customer.getId();
        dbmanager.insertQuery(query);
    }
    
    public void deleteCategory(int ID) {
        String query = "DELETE FROM categorie WHERE categorie_id=" + ID;
        dbmanager.insertQuery(query);
        query = "DELETE FROM product WHERE categorie_id=" + ID;
        dbmanager.insertQuery(query);
    }
    /*
     * Updates the customer
     * @param Customer customer
     */
    public void updateCategory(Category category) {
        String SQL = "UPDATE categorie SET naam='" + category.getName() + "', omschrijving='"
                + category.getDescription() 
                +  "' WHERE categorie_id='"
                + category.getCategoryId() + "'";
        dbmanager.insertQuery(SQL);
    }
    
    /*
     * Updates the customer
     * @param Customer customer
     */
    public void updateProduct(Product product) {
        String SQL = "UPDATE product SET naam='" + product.getName() + "', prijs='" + product.getPrice() + "', omschrijving='"
                + product.getDescription() 
                +  "' WHERE product_id='"
                + product.getProductId() + "'";
        dbmanager.insertQuery(SQL);
    }
    
    /*
     * Gets the amount of sales of the product
     * @param Product product
     * @return int
     */
    public int getProductSales(Product product) {
        int amount = 0;
        try {
            String query = "SELECT aantal FROM orderregel WHERE product_id = " + product.getProductId();
            ResultSet result = dbmanager.doQuery(query);
            while (result.next()) {
                amount = result.getInt("aantal");
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return amount;
    }
    
    /*
     * Geeft de totale omzet
     * @return double
     */
    public double getTotaleOmzet() {
        double amount = 0;
        try {
            String query = "SELECT SUM(product.prijs*orderregel.aantal) AS total "
                    + "FROM orderregel JOIN product "
                    + "ON orderregel.product_id = product.product_id";
            ResultSet result = dbmanager.doQuery(query);
            while (result.next()) {
                amount = result.getFloat("total");
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return amount;
    }
    
    /*
     * Geeft de omzet per product
     * @return List<Product>
     */
    public List<Product> getOmzetPerProduct() {
        List<Product> products = new ArrayList<Product>();
        try {
            String query = "SELECT product.product_id, product.categorie_id, "
                    + "product.naam, orderregel.aantal*product.prijs AS prijs, "
                    + "product.omschrijving FROM orderregel "
                    + "LEFT JOIN product "
                    + "ON orderregel.product_id = product.product_id "
                    + "ORDER BY prijs DESC";
            ResultSet result = dbmanager.doQuery(query);
            while (result.next()) {
            products.add(new Product(result.getInt("product_id"),
                    result.getInt("categorie_id"),
                    result.getString("naam"),
                    result.getString("omschrijving"),
                    result.getDouble("prijs")
            ));
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return products;
    }
    
    public List<Product> getTop10() {
        List<Product> products = new ArrayList<Product>();
        try {
            String query = "SELECT "
                + "orderregel.aantal, product.product_id, product.categorie_id, "
                + "product.naam, product.prijs, product.omschrijving "
                + "FROM orderregel "
                + "LEFT JOIN product "
                + "ON orderregel.product_id = product.product_id "
                + "ORDER BY orderregel.aantal DESC LIMIT 0,10";
            ResultSet result = dbmanager.doQuery(query);
            while (result.next()) {
                products.add(new Product(result.getInt("product_id"),
                        result.getInt("categorie_id"),
                        result.getString("naam"),
                        result.getString("omschrijving"),
                        result.getDouble("prijs"),
                        result.getInt("aantal")));
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return products;
    }

    public List<Product> getProducts(int categoryId) {
        List<Product> products = new ArrayList<Product>();
        try {
            String sql = "SELECT * FROM product WHERE categorie_id='" + categoryId + "' ORDER BY naam ASC";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                products.add(new Product(result.getInt("product_id"),
                        result.getInt("categorie_id"),
                        result.getString("naam"),
                        result.getString("omschrijving"),
                        result.getDouble("prijs")));
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return products;
    }
    
    /*
     * Returns all products
     * @return List<Product>   
     */
    public List<Product> getProducten() {
        List<Product> products = new ArrayList<Product>();
        try {
            // String sql = "SELECT * FROM product WHERE categorie_id='" + categoryId + "' ORDER BY naam ASC";
            String sql = "SELECT * FROM product ORDER BY product_id ASC";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                products.add(new Product(
                        result.getInt("product_id"),
                        result.getInt("categorie_id"),
                        result.getString("naam"),
                        result.getString("omschrijving"),
                        result.getDouble("prijs")));
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return products;
    }
    
    //Returns list with categories
    public List<Category> getCategory() {
        List<Category> categories = new ArrayList<Category>();
        try {
            String sql = "SELECT * FROM categorie ORDER BY categorie_id ASC";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                categories.add(new Category(
                        result.getInt("categorie_id"),
                        result.getString("naam"),
                        result.getString("omschrijving")));
                //categories.add(category);
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return categories;
    }
    
    /////////////////////////////////////////////////////////////////////////
    // sets an order in the database.
    // slightly modified to return the orderID, through a very ugly method.
    public int setOrder(model.Basket basket, int klantID, String opmerking, String betaalmethode) {
        String SQL_order = "INSERT INTO `order` (klant_id, notes, betaalmethode, datum)"
                + " VALUES('" + klantID + "', '" + opmerking + "', '" + betaalmethode + "', CURDATE() )";
        int order_id = 0;
        try {
            ResultSet result = dbmanager.insertQuery(SQL_order);
            result.next();
            order_id = result.getInt(1);
        } catch (SQLException e) {
            System.out.println("connectivity.QueryManager.setOrder() Exception:" + e.getMessage());
        }
        List<Product> products = basket.getProducts();
        for (Product product : products) {
            int product_id = product.getProductId();
            int aantal = basket.getProductAmount(product);
            String SQL_orderProduct = "INSERT INTO orderregel (product_id,order_id,aantal) VALUES ("
                    + product_id + "," + order_id + "," + aantal + ")";
            dbmanager.insertQuery(SQL_orderProduct);
        }
        //return the ID of the last posted order. (should be this one, unless 2 people managed to simultaneously click the button.
        String sql_returnID = "select MAX(`order_id`) from `order`";
        ResultSet rs = dbmanager.doQuery(sql_returnID);
        try{
            rs.next();              //need to forward the resultset one row, otherwise it'll try reading non-existent data.
            return rs.getInt(1);    //Looks ugly, but for some reason the columnname wasn't being returned propely.
        }
        catch(SQLException e){
            return -1;              //Return -1 if the query failed.
        }
                
    }
    //Updates an existing order with new information
    //NOTE - Does not change the content of the basket

    public void updateOrder(Order order) {
        //Customer customer = order.getCustomer();
        String SQL = "UPDATE `order` SET notes='" + order.getNotes() + "', betaalmethode='"
                + order.getPMethod() + "', datum='" + order.getDate() +  "' WHERE order_id=" + order.getOrderID();
        dbmanager.insertQuery(SQL);
    }
    //creates and returns an Order object selected by orderID
    //if it returns an order with ID -1, something crashed horribly, or the ID doesn't exist.
    public model.Order getOrderByID(int ID) {
        //Create an order object.
        model.Order order = new model.Order();
        try {
            //Get the basic information of the order and place it into the object.
            String sql = "SELECT * FROM `order` WHERE order_id='" + ID +"'";
            ResultSet rs = dbmanager.doQuery(sql);
            rs.next();
            try{
                
                order.setDate(rs.getDate("datum") );
                order.setPaymentMethod(rs.getString("betaalmethode"));
                order.setNotes(rs.getString("notes"));
                order.setOrderID(ID);                       
                
                //Create a customer Object to add to the order.
                int klantID = rs.getInt("klant_id");
                //Reassign sql to a string meant for getting customerData.
                sql = "SELECT * FROM klant WHERE klant_id='" + klantID + "'";
                rs = dbmanager.doQuery(sql);
                rs.next();
                Customer customer = new Customer(klantID, rs.getString("naam"),
                    rs.getString("adres"), rs.getString("postcode"),
                    rs.getString("woonplaats"));
                order.setCustomer(customer);
                
                //Create the basket, reassign sql for filling the basket.
                model.Basket bs = new model.Basket();
                sql = "SELECT product_id, aantal FROM orderregel WHERE order_id='" + ID + "'";
                rs = dbmanager.doQuery(sql);
                //rs.next();
                
                //Loop through the resultset to see which products there are, and add them to the basket.
                Product tempProd = new Product();
                while (rs.next()) {
                    tempProd = getProduct(rs.getInt("product_id"));
                    for (int i = rs.getInt("aantal"); i > 0; i--) { //each count of a product has to be added individually.
                        bs.addProduct(tempProd);
                    }
                
                }
                //add basket to the order object.
                order.setBasket(bs);
            }
            catch(java.lang.NullPointerException er){
                order.setOrderID(-1);
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            order.setOrderID(-1);
        }
        return order;
    }

    public void deleteOrderByID(int orderID) {
        //Ugly, but it works. trying to combine the two Queries just generated errors.
        String sql = "delete from `order` where order_id='" + orderID + "'";
        dbmanager.insertQuery(sql);
        sql = "delete from orderregel where order_id='" + orderID + "'";
        dbmanager.insertQuery(sql);
    }
    
     public void newProduct(Product product) {
        String SQL = "INSERT INTO product (categorie_id, naam, prijs, omschrijving) VALUES ("
                + product.getCategorieId() + ", '" + product.getName() + "', " + product.getPrice() + ","
                + "'" + product.getDescription() + "')";
        dbmanager.insertQuery(SQL);
    }
     
    /////////////////////////////////////////////////////////////////////////////////////////////////////
}

